﻿CREATE PROCEDURE [dbo].[proc_Statistics_Financial_Tongji_ZiJin_GroupBYDate_G]
	(
		@CompanyId int,
		@StartDate nchar(10),
		@EndDate nchar(10),
		@StartIndex int,
		@EndIndex int
	)
AS
Begin	
	
	DECLARE @strDate DATETIME
	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime, @StartDate + ' 00:00:00')
	Set @eRq = Convert(Datetime, @EndDate + ' 23:59:59')
	
	--定义时间临时表
	declare @TempSca table(d_str DATETIME )
	set @strDate=CONVERT(DATETIME, @StartDate)
	WHILE @strDate<=CONVERT(DATETIME,@EndDate)
	BEGIN
		INSERT INTO @TempSca(d_str) VALUEs(@strDate)
		set @strDate=DATEADD(d,1, @strDate)
	END
	
	 ;WITH list As(
	 SELECT 
	 CreateDate,ISNULL(IBA,0) AS IBA,ISNULL(IBB,0) as IBB,ISNULL(IBD,0) AS IBD,ISNULL(IBE,0) as IBE,isnull(IBC,0) AS IBC,isnull(IBF,0) AS IBF,
	 isnull(IBG,0) AS IBG,ISNULL((ibj),0) AS IBJ,isnull(IBK,0) AS IBK,isnull(IBL,0) AS  IBL
	 
	 ,isnull(IBH,0) AS IBH
	 
	 ,isnull(IBI,0) AS IBI
	 FROM Statistics_Financial  AS a
	 
	 Where CompanyId=@CompanyId and CreateDate BETWEEN @sRq AND @eRq
	 ) 
	 
	 SELECT *,(SELECT COUNT(1) FROM @TempSca ) AS cid FROM (
	  SELECT ROW_NUMBER() OVER (ORDER BY CreateDate)AS Row,* FROM (	Select * From list
	--,(SELECT SUM(IBA) AS IBA_a,SUM(IBB) AS IBB_a,SUM(IBD) AS IBD_a,SUM(IBE) AS IBE_a
	--,SUM(IBC) AS IBC_a,SUM(IBF) AS IBF_a,SUM(IBG) AS IBG_a,SUM(IBH) AS IBH_a
	--,SUM(IBI) AS IBI_a,SUM(IBJ) AS IBJ_a,SUM(IBK) AS IBK_a,SUM(IBL) AS IBL_a	
	-- FROM list) AS n  
	UNION 
	SELECT d_str,0,0,0,0,0,0,0,0,0,0,0,0 FROM @TempSca WHERE d_str NOT IN (SELECT Convert(nchar(10),CreateDate,120) FROM list )
	 ) AS nb ) AS nb1
	 
	  WHERE  Row Between @StartIndex and @EndIndex
	  ORDER BY CreateDate
	
	
	
	 
	 
End
